<?php
/**
 * CodeXplorer System
 *
 * ABSTRACT
 *
 * Provides a database abstraction layer to a MySQL database.
 *
 * NOTES
 *
 * This db class is about 2x slower than the original mysql functions
 * Total time of 1.5326013565063 = 0.01533 sec Average for MySQL func
 * Total time of 3.6232841014862 = 0.03623 sec Average for MySQL.class.php
 * 
 * The test ran a for() loop 20 times for both versions, 100 times in a row.
 * So each type of database script was run 2000 times by the scripts end. 
 * Both were selecting 20 text fields from a small db.
 *
 * @package    /includes/db/mysql.db.class.php
 * @version    1.0.0 <4/5/2008>
 * @author     David Pennington <@codexplorer.com>
 * @copyright  Copyright (c) 2008 CodeXplorer <http://www.codexplorer.com>
 * @license    http://www.gnu.org/licenses/gpl-3.0.html (GPL v3)
 *
 ********************************** 80 Columns *********************************
 */



class mysql_cx_db extends cx_db {
    
    private $error_messages = array (
                'Require $dbuser and $dbpassword to connect to a database server',
                'Error establishing mySQL database connection. Correct user/password? Correct hostname? Database server running?',
                'Could not select database',
                'mySQL database connection is not active',
                'Unexpected error while trying to select database',
                'Error with MySQL query',
                '<b>Fatal Error:</b> mysql.db.class requires mySQL Lib to be compiled and or linked in to the PHP engine',
                '<b>Fatal Error:</b> mysql.db.class requires db.class to be included/loaded before it can be used'
    );

    ///////////////////////////////////////////////////
    // Constructor
    function __construct($db_info) {
        
        $this->db_user  = $db_info['db_user'];
        $this->db_pass  = $db_info['db_pass'];
        $this->db_name  = $db_info['db_name'];
        $this->db_host  = $db_info['db_host'];
        $this->db_persistent = $db_info["db_persistent"];
        $this->db_type  = 'mysql';
        
        if (!function_exists('mysql_connect'))
            trigger_error($this->$error_messages[5], E_USER_ERROR);
            
	//if (!class_exists('CX_DB')) 
            //trigger_error($this->$error_messages[6], E_USER_ERROR);
        
        //Try to connect now
        $this->connect();
        
        return true;
    }


    ///////////////////////////////////////////////////
    // Connect to a MySQL DB
    public function connect($db_info=null) {
    
        //To keep E_NOTICE's happy lets wrap in an if
        if($db_info) {
            //If no info was supplied - use constructor set variables
            if ($db_info['db_user']) { $this->db_user = $db_info['db_user']; }
            if ($db_info['db_pass']) { $this->db_pass = $db_info['db_pass']; }
            if ($db_info['db_name']) { $this->db_name = $db_info['db_name']; }
            if ($db_info['db_host']) { $this->db_host = $db_info['db_host']; }
            //Set the type of connection
            if ($db_info["db_persistent"]) { $this->db_persistent = $db_info["db_persistent"]; }
        }
        
        //If it is a persistent connection (keeps running after script ends)
        if($this->db_persistent) {
            
            // Try to establish the persistent server database handle
            $this->db_handle = mysql_pconnect($this->db_host,$this->db_user,$this->db_pass)
                        or trigger_error('(MySQL Error: '. mysql_errno() .') '. mysql_error(). $this->error_messages[1], E_USER_ERROR);
            
        } else {
        
            // Try to establish the server database handle
            $this->db_handle = mysql_connect($this->db_host,$this->db_user,$this->db_pass)
                        or trigger_error('(MySQL Error: '. mysql_errno() .') '. mysql_error(). $this->error_messages[1], E_USER_ERROR);
            
        }
        
        //Now select the actual DB we will be using
        $this->select();
        
        //The return the resource ID of the connection
        return $this->db_handle;
    }

    ///////////////////////////////////////////////////
    // Close a MySQL connection
    public function close($db_handle) {
    
    	// Closing connection
        mysql_close($db_handle) 
            or trigger_error('(MySQL Error: '. mysql_errno() .') '. mysql_error(), E_USER_ERROR);
        
    }

    ///////////////////////////////////////////////////
    // Select a MySQL DB
    public function select($db_name=null) {
    
        //If the database name was passed to this function
        if ($db_name) { $this->db_name = $db_name; }
        //Else we will use the db_name already set by connect() or Constructor
    
        mysql_select_db($this->db_name, $this->db_handle) 
            or trigger_error('(MySQL Error: '. mysql_errno() .') '. mysql_error(), E_USER_ERROR);
        
    }

    /*
    ///////////////////////////////////////////////////
    // Create a DB
    function database($action, $db_name=null) {
    	
        if ($db_name) { $this->db_name = $db_name; }
        
        if($action = 'create') {
            return $this->query("CREATE DATABASE ". $this->db_name);
        } else if($action = 'drop') {
            return $this->query("DROP DATABASE ". $this->db_name);
        }
    }
    */
    
    
    ///////////////////////////////////////////////////
    // Show all tables of the selected Database
    public function tables($like=null, $where=null) {
    	
        //Show all tables as a database
        $this->query('SHOW TABLES'. ($like ? ' LIKE \'%'. $like. '%\'' : ''). ($where ? ' WHERE '. $where : ''));
        
        //For each table
        while ($row = $this->fetch('row')) {
            //Add the table name to our array
            $tables[] = $row[0];
        }
        
        //return an array of the table names
        return $tables;
        
    }
    
    
    ///////////////////////////////////////////////////
    // Show all columns/fields within a table
    public function columns($table=null) {
    	
        //if the table name is empty/null
        if(!$table) { return; }
        
        //Show all tables as a database
        $this->query('SHOW COLUMNS FROM '. $table);
        
        /* RESULT:
        Array
        (
            [Field] => id
            [Type] => int(7)
            [Null] =>  
            [Key] => PRI
            [Default] =>
            [Extra] => auto_increment
        )
        */
        
        //Define the variable
        $columns = array();
        
        //If we found some rows
        if ($this->found_rows() > 0) {
            while ($row = $this->fetch()) {
            
                //If we found the "ENUM" column name in the string (we need to get the values)
                if(strpos($row['Type'], 'enum') !== false) {
                
                    $row['type'] = 'enum';
                    $row['length'] = null;
                    
                    //Get the posible enum values for this column (i.e. "enum('active','disabled')")
                    $options = explode("','", preg_replace("/(enum|set)\('(.+?)'\)/","\\2", $row['Type']));
                    foreach ($options as $value) {
                        $row['values'][] = $value;
                    }
                
                //Else it is a varchar, text, blob, int, etc..
                } else {
                
                    //We need to break the "Type" value from "int(20)" into "int" and "20"
                    preg_match('/([a-z]+)(\(([0-9]{1,5})\))?/i', $row['Type'], $matches);
                    
                    //Add column type
                    $row['type'] = $matches[1];
                    $row['values'] = null;
                    
                    //If there was a number (i.e. 11) on the Type value 
                    //add it to a new array element called "Length"
                    $row['length'] = (isset($matches[3]) ? $matches[3] : null);
                    
                }
                
                //Create a custom array descibing the column
                $columns[strtolower($row['Field'])] = array('name' => $row['Field'], 'default' => $row['Default'], 'key' => $row['Key'],
                                    'length' => $row['length'],'type' => $row['type'], 'values' => $row['values']);
                
                //NOTE: Not all DB's are like MySQL so we can't just return ($columns[] = $row)
                //Instead we must make an array with values that are common among DB's (like "type", "length", and "name")
            }
        }
        
        //Return an array containing columns and their values
        return $columns;
        
    }

    
    ///////////////////////////////////////////////////
    // Show all columns/fields within a table
    public function fields($table=null) {
    	
        //if the table name is empty/null
        if(!$table) { return; }
        
        //Show all tables as a database
        $this->query('SELECT * FROM '. $table);
        
        /* RESULT:
        stdClass Object
        (
            [name] => id
            [table] => users
            [def] => 
            [max_length] => 2
            [not_null] => 1
            [primary_key] => 1
            [multiple_key] => 0
            [unique_key] => 0
            [numeric] => 1
            [blob] => 0
            [type] => int
            [unsigned] => 0
            [zerofill] => 0
        )
        */

        $number = $this->number_of_fields();
        
        //While $x is less than the number of columns/fields
        for($x=0;$x<$number;$x++) {
        
            //Add each columns' data to the array
            $fields[] = mysql_fetch_field($this->result, $x);
        
        }
        
        //Return an array containing columns and their values
        return $fields;
        
    }
    
    
    ///////////////////////////////////////////////////
    // Return the number of columns/fields in a request
    public function number_of_fields($result=null) {
    	
        //If a result was passed use it instead of the last result
        if(is_resource($result)) { $this->result = $result; }
        
        //Return the number of fields
        return mysql_num_fields($this->result);
        
    }
    
    
    ///////////////////////////////////////////////////
    // Basic MySQL Query
    public function query($query, $buffer=true) {
    	
        // Increase the query count
        $this->total_queries++;
        
        global $queries_run_this_page;
        //trigger_error($query);
        $queries_run_this_page .= $query. '<br />';
        
        // If there is no existing database connection then try to connect
        if (!$this->db_handle) {
            $this->connect();
        }
        
        if($buffer) {
            // Perform a buffered query
            $result = mysql_query($query, $this->db_handle) 
                or trigger_error('(MySQL Error: '. mysql_errno() .') '. mysql_error(), E_USER_ERROR);
        } else {
            // Perform an unbuffered query
            $result = mysql_unbuffered_query($query, $this->db_handle) 
                or trigger_error('(MySQL Error: '. mysql_errno() .') '. mysql_error(), E_USER_ERROR);
        }
        
        //If you want to see if the query was successful
        //var_dump($this->result);
        
        //If there were no errors then set this->result to the $result
        if(is_resource($result)) { $this->result = $result; }
        
        return $result;
        
    }


    ///////////////////////////////////////////////////
    // MySQL result fetch
    public function fetch($type=null, $result=null) {
    
        //If a result was passed use it instead of the last result
        if(is_resource($result)) { $this->result = $result; }
        
        //Return the row in the way the user wants it
        if($type == 'object') {
            return mysql_fetch_object($this->result);
        } elseif ($type == 'row') {
            return mysql_fetch_row($this->result);
        } else {
            return mysql_fetch_assoc($this->result);
        }
    }


    ///////////////////////////////////////////////////
    // Fetch a single result - like the result of COUNT(*)
    public function fetch_value($result=null) {
    	
        //If a result was passed use it instead of the last result
        if(is_resource($result)) { $this->result = $result; }
        
        return mysql_result($this->result, 0);
        
    }
    
    
    ///////////////////////////////////////////////////
    // Number of DB rows affected by last query
    public function affected_rows($result=null) {

        //If a result was passed use it instead of the last result
        if(is_resource($result)) { $this->result = $result; }
        
        return mysql_affected_rows($this->result);
        
    }


    ///////////////////////////////////////////////////
    // Number of DB rows found by last query
    public function found_rows($result=null) {
    
        //If a result was passed use it instead of the last result
        if(is_resource($result)) { $this->result = $result; }
        
        return mysql_num_rows($this->result);
    }


    ///////////////////////////////////////////////////
    // ID generated from the previous INSERT operation
    public function last_row_id($result=null) {
    
        //If a result was passed use it instead of the last result
        if(is_resource($result)) { $this->result = $result; }
        
    	return mysql_insert_id($this->result);
    }
    

    ///////////////////////////////////////////////////
    // Format a string correctly for safe insert (shouldn't be used)
    public function escape($value=null) {
        return mysql_real_escape_string($value);
    }
    
    
    ///////////////////////////////////////////////////
    // Kill cached variables
    public function clean() {
    	// Clean value
    	$this->result = null;
    }
    
}



